dim_datetable

Details
Tags
untagged
Owner
TRANSFORMER
Type
table
Package
gitlab_snowflake
Relation
PROD.common.dim_date
Approximate Size
818 KB
Last Modified
2023-07-10 23:06UTC
Row Count
-1
Description
Dimensional table representing both calendar year and fiscal year date details. The grain of the table is a calendar day. Information on the Enterprise Dimensional Model can be found in the [handbook](https://about.gitlab.com/handbook/business-ops/data-team/platform/edw/)
Columns
ColumnTypeDescriptionTestsMore?
date_id
NUMBERUN
date_day
DATECalendar date, e.g. '2019-02-02'
date_actual
DATEMatches `date_day`, duplicated for ease of use
day_name
TEXTAbbreviated name of the day of the week, e.g. 'Sat' for 2019-02-02
month_actual
NUMBERNumber for the calendar month of the year, e.g. '2' for 2019-02-02
year_actual
NUMBERCalendar year, e.g. '2019' for 2019-02-02
quarter_actual
NUMBERCalendar quarter, e.g. '1' for 2019-02-02
day_of_week
NUMBERNumber of the day of the week, with Sunday = 1 and Saturday = 7
first_day_of_week
DATECalendar date of the first Sunday of that week, e.g. '2019-01-27' for 2019-02-02
week_of_year
NUMBERCalendar week of year, e.g. '5' for 2019-02-02
day_of_month
NUMBERDay Number of the month, e.g. '2' for 2019-02-02
day_of_quarter
NUMBERDay Number from the start of the calendar quarter, e.g. '33' for 2019-02-02
day_of_year
NUMBERDay Number from the start of the calendar year, e.g. '33' for 2019-02-02
fiscal_year
NUMBERFiscal year for the date, e.g. '2020' for 2019-02-02
fiscal_quarter
TEXTFiscal quarter for the date, e.g. '1' for 2019-02-02
day_of_fiscal_quarter
NUMBERDay Number from the start of the fiscal quarter, e.g. '2' for 2019-02-02
day_of_fiscal_year
NUMBERDay Number from the start of the fiscal year, e.g. '2' for 2019-02-02
month_name
TEXTThe full month name for any calendar month, e.g. 'February' for 2019-02-02
first_day_of_month
DATEThe first day of a calendar month, e.g. '2019-02-01' for 2019-02-02
last_day_of_month
DATEThe last day of a calendar month, e.g. '2019-02-28' for 2019-02-02
first_day_of_year
DATEThe first day of a calendar year, e.g. '2019-01-01' for 2019-02-02
last_day_of_year
DATEThe last day of a calendar year, e.g. '2019-12-31' for 2019-02-02
first_day_of_quarter
DATEThe first day of a calendar quarter, e.g. '2019-01-01' for 2019-02-02
last_day_of_quarter
DATEThe last day of a calendar quarter, e.g. '2019-03-31' for 2019-02-02
first_day_of_fiscal_quarter
DATEThe first day of the fiscal quarter, e.g. '2019-02-01' for 2019-02-02
last_day_of_fiscal_quarter
DATEThe last day of the fiscal quarter, e.g. '2019-04-30' for 2019-02-02
first_day_of_fiscal_year
DATEThe first day of the fiscal year, e.g. '2019-02-01' for 2019-02-02
last_day_of_fiscal_year
DATEThe last day of the fiscal year, e.g. '2020-01-31' for 2019-02-02
week_of_fiscal_year
NUMBERThe week number for the fiscal year, e.g. '1' for 2019-02-02
month_of_fiscal_year
NUMBERThe month number for the fiscal year, e.g. '1' for 2019-02-02
last_day_of_week
DATEThe Saturday of the week, e.g. '2019-02-02' for 2019-02-02
quarter_name
TEXTThe name of the calendar quarter, e.g. '2019-Q1' for 2019-02-02
fiscal_quarter_name
TEXTThe name of the fiscal quarter, e.g '2020-Q1' for 2019-02-02
fiscal_quarter_name_fy
TEXTThe name of the fiscal quarter, e.g 'FY20-Q1' for 2019-02-02
fiscal_quarter_number_absolute
NUMBERMonotonically increasing integer for each fiscal quarter. This allows for comparing the relative order of fiscal quarters.
fiscal_month_name
TEXTThe name of the fiscal month, e.g '2020-Feb' for 2019-02-02
fiscal_month_name_fy
TEXTThe name of the fiscal month, e.g 'FY20-Feb' for 2019-02-02
holiday_desc
TEXTThe name of the holiday, if applicable
is_holiday
BOOLEANWhether or not it is a holiday
last_month_of_fiscal_quarter
DATEDate indicating last month of fiscal quarter e.g '2020-04-01' for 2020-02-02
is_first_day_of_last_month_of_fiscal_quarter
BOOLEANFlag indicating date that is the first day of last month of fiscal quarter. E.g TRUE for '2020-04-01'
last_month_of_fiscal_year
DATEDate indicating last month of fiscal year e.g '2021-01-01' for 2020-02-02
is_first_day_of_last_month_of_fiscal_year
BOOLEANFlag indicating date that is the first day of last month of fiscal year. E.g TRUE for '2021-01-01'
snapshot_date_fpa
DATE8th calendar day of a month used for FP&A snapshots
snapshot_date_billings
DATE45 calendar day after a month begins used for Billings snapshots
days_in_month_count
NUMBERNumber of calendar days in the given month.
week_of_month_normalised
NUMBER
day_of_fiscal_quarter_normalised
NUMBER
week_of_fiscal_quarter_normalised
NUMBER
day_of_fiscal_year_normalised
NUMBER
is_first_day_of_fiscal_quarter_week
NUMBER
days_until_last_day_of_month
NUMBER
current_date_actual
DATE
current_fiscal_year
NUMBER
current_first_day_of_fiscal_year
DATE
current_fiscal_quarter_name_fy
TEXT
current_first_day_of_month
DATE
created_by
TEXT
updated_by
TEXT
model_created_date
DATE
model_updated_date
DATE
dbt_updated_at
TIMESTAMP_LTZ
dbt_created_at
TIMESTAMP_LTZ
Referenced By
Depends On
Code
{{ config({
    "alias": "dim_date"
}) }}

WITH dates AS (

  SELECT
    {{ dbt_utils.star(
           from=ref('prep_date'), 
           except=['CREATED_BY','UPDATED_BY','MODEL_CREATED_DATE','MODEL_UPDATED_DATE','DBT_UPDATED_AT','DBT_CREATED_AT']
           ) 
      }}
  FROM {{ ref('prep_date') }}

), final AS (

  SELECT
    {{ get_date_id('date_actual') }}                                AS date_id,
    *
  FROM dates

)

{{ dbt_audit(
    cte_ref="final",
    created_by="@msendal",
    updated_by="@pempey",
    created_date="2020-06-01",
    updated_date="2023-01-09"
) }}
icons